#!/usr/bin/python
# from importlib.resources import path
import os
import sqlite3
from s2c.rules.conf import DB_NAME, FILE_TAB, PROCESS_TAB


# 数据库帮助
class DbHelper:
    def __init__(self):
        self.db_name = DB_NAME
        # 创建对象时建立数据库链接
        self.conn = sqlite3.connect(self.db_name)

    def init(self):
        self.close()
        self.clear()
        self.conn = sqlite3.connect(self.db_name)
        self.create_file_table()
        self.create_process_table()

    def close(self):
        # 关闭数据库
        self.conn.close()

    def clear(self):
        if os.path.exists(self.db_name):
            os.remove(self.db_name)

    # 创建table表，如果表已经存在不做创建
    def execute(self, *cmds):
        try:
            # 如果不存在创建表
            for cmd in cmds:
                self.conn.execute(cmd)
        except Exception as e:
            print('execute failed ', cmds, str(e))
            return False
        self.conn.commit()
        return True

        # 创建table表，如果表已经存在不做创建

    def create_index(self, index_cmd):
        try:
            # 如果不存在创建表
            self.conn.execute(index_cmd)
        except Exception as e:
            print('Create table index failed ', index_cmd)
            print(str(e))
            return False
        self.conn.commit()
        return True

    # 批量插入数据
    def batch_insert(self, table_cmd, items):
        ret = 0
        cursor = self.conn.cursor()
        try:
            cursor.executemany(table_cmd, items)
            ret = cursor.lastrowid
        except Exception as e:
            print(f'batch_insert table {table_cmd} failed', str(e))
            return ret
        self.conn.commit()
        print('batch_insert success!')
        return ret

    # 单笔插入数据
    def insert(self, table, item):
        ret = 0
        cursor = self.conn.cursor()
        try:
            cursor.execute(table, item)
            ret = cursor.lastrowid
        except Exception as e:
            print(f'insert table{table} failed', str(e))
            return ret
        self.conn.commit()
        return ret

    # 创建 file_tab 表
    def create_file_table(self):
        create_cmd = f'CREATE TABLE IF NOT EXISTS {FILE_TAB}' + '''
		(   id INTEGER PRIMARY KEY AUTOINCREMENT,
            path TEXT, --路径
            md5 TEXT, 
            mode TEXT,
            dict_type TEXT,
            link TEXT,
            desc TEXT
        );
		'''
        index_cmd = f'CREATE UNIQUE INDEX path ON {FILE_TAB} (path);'
        index2_cmd = f'CREATE UNIQUE INDEX md5 ON {FILE_TAB} (md5);'
        index3_cmd = f'CREATE INDEX file_dict_type ON {FILE_TAB} (dict_type);'
        return self.execute(create_cmd, index_cmd, index2_cmd, index3_cmd)

    # 创建 process_tab 表
    def create_process_table(self):
        create_cmd = f'CREATE TABLE IF NOT EXISTS {PROCESS_TAB}' + '''
		(   id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT,
            dict_type TEXT, 
            username TEXT,
            cmdline TEXT,
            exe TEXT,
            status TEXT,
            pid TEXT,
            ppid TEXT
        );
		'''
        index_cmd = f'CREATE UNIQUE INDEX name ON {PROCESS_TAB} (name);'
        index2_cmd = f'CREATE INDEX ps_dict_type ON {PROCESS_TAB} (dict_type);'
        return self.execute(create_cmd, index_cmd, index2_cmd)

    #创建table表，如果表已经存在不做创建
    def create_scan_status_table(self):
        table_str = '''
        CREATE TABLE IF NOT EXISTS FILESCANSTATUS
        (id INTEGER PRIMARY KEY AUTOINCREMENT,
        dir INT DEFAULT 0,
        file INT DEFAULT 0,
        other INT DEFAULT 0,
        count INT DEFAULT 0,
        status INT DEFAULT 0,
        time INT DEFAULT 0,
        new_count INT DEFAULT 0,
        create_time TEXT);
        '''
        try:
            # 如果不存在创建表
            self.conn.execute(table_str)
        except Exception as e:
            print('Create table failed ', table_str)
            print(str(e))
            return False
        self.conn.commit()
        return True

    # 插入file scan table的数据，参数数组
    def insert_db_scan_file_table(self, obj):
        # 如果数据表不存在，先创建
        self.create_scan_status_table()
        str_key, str_value,	persons = self.get_key_value(obj)
        #persons = (obj['dir'], obj['file'], obj['other'], obj['count'], obj['status'], obj['time'], obj['new_count'], obj['create_time'])
        inser_data = 'insert into FILESCANSTATUS(' + str_key + ') values (' + str_value + ')'
        return self.insert(inser_data, persons)

    # 更新数据状态
    def update_db_scan_file_status(self, id, obj):
        runexec = self.conn.cursor()
        '''
        obj = {
            'dir': file,
            'file': path,
            'other': name[1].strip(),
            'count': md5,
            'time': type # 0表系统default值,
            'status': 0,
            'new_count':0
        }
        '''
        try:
            #up_data = 'UPDATE FILESCANSTATUS SET dir=' + str(obj['dir']) + ', file=' + str(obj['file']) + ', other=' + str(obj['other']) + ', count=' + str(obj['count']) + ', time=' + str(obj['time']) + ', status=' + str(obj['status']) + ' WHERE id=' + str(id)
            up_data = 'UPDATE FILESCANSTATUS SET ' 
            first = True
            for item in obj.keys():
                # 第一个不需要增加逗号
                if first:
                    up_data += str(item) + '=' + str(obj[item])
                    first = False
                else:
                    up_data += ', ' + str(item) + '=' + str(obj[item])

            up_data += ' WHERE id=' + str(id)
            #print('------update_db_scan_file_status----------', up_data)
            runexec.execute(up_data)
            self.conn.commit()
            self.get_db_scan_file_status(id)
            return True
        except Exception as e:
            print("update FILESCANSTATUS file table failed")
            print(str(e))		
        return False

    # 查询扫描文件状态
    # 返回文件数组
    def get_db_scan_file_status(self, id):
        runexec = self.conn.cursor()
        ret_obj= {
            'dir': 0,
            'file': 0,
            'other': 0,
            'count': 0,
            'status': 0,
            'time': 0,
            'new_count': 0,
            'create_time': ''
        }
        try:
            # 查询扫描出来的系统文件
            runexec.execute('SELECT * FROM FILESCANSTATUS WHERE id=' + str(id))
            # 如果查询到该文件不做复制动作,返回FALSE
            allrows = runexec.fetchall()
            if len(allrows):
                for row in allrows:
                    ret_obj['id'] = row[0]
                    ret_obj['dir'] = row[1]
                    ret_obj['file'] = row[2]
                    ret_obj['other'] = row[3]
                    ret_obj['count'] = row[4]
                    ret_obj['status'] = row[5]
                    ret_obj['time'] = row[6]
                    ret_obj['new_count'] = row[7]
                    ret_obj['create_time'] = row[8]
        except Exception as e:
            print('get_db_file_san_status failed ', id)
            print(str(e))

        #print('get_db_file_san_status ', ret_obj)
        return ret_obj

    #根据对象返回key和value、persons值
    def get_key_value(self, obj):
        str_key = ''
        str_value = ''
        persons = '' #虽然说元组不可变，但是它还是有个被支持的骚操作——元组之间进行连接组合
        first = True
        for item in obj.keys():
            if first:
                str_key += str(item)
                str_value += '?'
                persons = (obj[item],)
                first = False
            else:
                str_key += ', ' + str(item)
                str_value += ',?'
                persons += (obj[item],)
        return str_key, str_value, persons


if __name__ == "__main__":
    db = DbHelper()
    db.init()
